﻿using Dapper;
using Domain.Entity;
using Domain.Entity.sys;
using Domain.IRepository;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EFCore.Repository
{
    public class DictDataRepository : IDictDataRepository
    {

        //连接字符串
        string con = "Data Source=121.37.93.71;Initial Catalog=CSSL_ZCTWMS;User ID=sa;Password=geili@2025;Encrypt=False";


        //添加字典数据
        public async Task<int> AddDictData(DictData dictData)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"insert into [dbo].[sys_dict_data](dict_sort, dict_label, dict_value, is_default, dict_id, dict_type, status, create_name, create_time,remark)
                                values({dictData.dict_sort},'{dictData.dict_label}','{dictData.dict_value}',{dictData.is_default},{dictData.dict_id},'{dictData.dict_type}',{dictData.status},'{dictData.create_name}','{dictData.create_time}','{dictData.remark}')";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }

        //删除字典数据
        public async Task<int> DeleteDictData(int id)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"update [dbo].[sys_dict_data] set is_delete=1 where dict_code={id}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }

        //根据字典类型查询数据
        public async Task<List<DictData>> GetAllDictDataBytype(int typeid)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"select dict_label,dict_value from [dbo].[sys_dict_data] where dict_id={typeid} and is_delete=0";
                return  sqlConnection.Query<DictData>(sql).ToList();
            }
        }


        //根据id查询字典数据
        public async Task<DictData> GetDictDataById(int id)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"select * from [dbo].[sys_dict_data] where dict_code={id}";
                return await sqlConnection.QueryFirstAsync<DictData>(sql);
            }
        }


        //根据字典类型分页查询数据
        public async Task<FenYe<DictData>> GetDictDataByType(FenYe<DictData> fenYe, string? label, int? status, string? createname, string? starttime, string? endtime, int typeid)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 分页存储过程
                DynamicParameters param = new DynamicParameters();
                param.Add("@pageSize", 10);
                param.Add("@keyName", "dict_code");
                param.Add("@tabelName", "sys_dict_data");
                param.Add("@currentPage", fenYe.CurrentPage);
                param.Add("@rows", dbType: DbType.Int32, direction: ParameterDirection.Output);
                //判断条件
                string sqlparam = $" is_delete=0 and dict_id={typeid}";
                if (!string.IsNullOrEmpty(label))
                    sqlparam += $" and dict_label like '%{label}%'";
                if (!string.IsNullOrEmpty(createname))
                    sqlparam += $" and create_name like '%{createname}%'";
                if (status != null && status != 0)
                    sqlparam += $" and  status={status}";
                if (!string.IsNullOrEmpty(starttime) || !string.IsNullOrEmpty(endtime))
                    sqlparam += $" and  create_time between '{starttime}' and '{endtime}'";

                param.Add("@where", sqlparam);

                string sql = "exec [dbo].[Fenye] @pageSize, @keyName, @tabelName, @where, @currentPage, @rows output";
                List<DictData> list = sqlConnection.Query<DictData>(sql, param).ToList();
                fenYe.List = list;
                fenYe.Rows = param.Get<int>("@rows");
                return fenYe;
            }
        }


        //修改字典数据
        public async Task<int> UpdateDictData(DictData dictData)
        {
            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();
                string sql = $@"update [dbo].[sys_dict_data] set dict_sort={dictData.dict_sort},dict_label='{dictData.dict_label}',dict_value='{dictData.dict_value}',is_default={dictData.is_default},dict_id={dictData.dict_id},dict_type='{dictData.dict_type}',status={dictData.status},update_name='{dictData.update_name}',update_time='{dictData.update_time}',remark='{dictData.remark}' where dict_code={dictData.dict_code}";
                return await sqlConnection.ExecuteAsync(sql);
            }
        }

        // 批量查询所有指定调拨ID的数据
        public async Task<List<DictData>> GetIds(int[] ids)
        {
            // 如果传入空数组，直接返回空结果
            if (ids == null || ids.Length == 0)
                return new List<DictData>();

            using (SqlConnection sqlConnection = new SqlConnection(con))
            {
                await sqlConnection.OpenAsync();

                // 使用参数化IN查询防止SQL注入
                string sql = $@"SELECT [dict_code],[dict_sort],[dict_label],[dict_value],[is_default],[dict_id],[dict_type],[status],[create_name],[create_time],[update_name],[update_time],[is_delete],[remark]
                                FROM [dbo].[sys_dict_data]
                                WHERE dict_id IN @ids";

                // 直接传递数组参数，Dapper会自动展开为参数化查询
                var parameters = new { ids };

                // 执行查询并返回结果
                return (await sqlConnection.QueryAsync<DictData>(sql, parameters)).ToList();
            }
        }
    }
}
